import pandas as pd
import numpy as np
from sklearn.metrics import accuracy_score, mean_squared_error, r2_score, mean_squared_log_error, mean_absolute_error
from sklearn.preprocessing import StandardScaler,MinMaxScaler
from scipy.spatial import distance
from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression, SGDRegressor
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor, AdaBoostRegressor, BaggingRegressor, ExtraTreesRegressor
import sklearn.model_selection as ms
from sklearn.neighbors import KNeighborsRegressor
import xgboost as xgb
from sklearn.linear_model import Lasso, Ridge, ElasticNet
import datetime
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
from SKO.AbstractDPJob import AbstractDPJob
from Predict_6621Job import Predict_6621Job
from Predict_6623Job import Predict_6623Job

class Predict_6624Job(AbstractDPJob):
    def __init__(self,
                 p_mode=None,p_bf_no=None,p_avg_iron_temp=None,p_avg_c_s_value=None,p_compute_slag_rate=None,
                 p_compute_fill_s_value=None, p_zongjiaobi=None, p_create_date=None,
                 p_old_vm=None, p_old_s=None, p_old_unit_price=None,
                 p_new_vm=None, p_new_s=None, p_new_unit_price=None,
                 p_old_jiaotan_s=None, p_new_jiaotan_s=None):

        super(Predict_6624Job, self).__init__()
        self.mode = p_mode
        self.bf_no = p_bf_no
        self.avg_iron_temp = p_avg_iron_temp
        self.avg_c_s_value = p_avg_c_s_value
        self.compute_slag_rate = p_compute_slag_rate
        self.compute_fill_s_value = p_compute_fill_s_value

        self.zongjiaobi = p_zongjiaobi

        self.create_date = p_create_date

        self.old_vm = p_old_vm
        self.old_s = p_old_s
        self.old_unit_price = p_old_unit_price
        self.new_vm = p_new_vm
        self.new_s = p_new_s
        self.new_unit_price = p_new_unit_price

        self.old_jiaotan_s = p_old_jiaotan_s
        self.new_jiaotan_s = p_new_jiaotan_s

        pass


    def execute(self):
        return self.do_execute()


    def do_execute(self):
        super(Predict_6624Job, self).do_execute()
        #预测铁水硫接口传入参数
        msg = ''
        result_list = []
        mode = self.mode
        bf_no = self.bf_no
        avg_iron_temp = self.avg_iron_temp
        avg_c_s_value = self.avg_c_s_value
        compute_slag_rate = self.compute_slag_rate
        compute_fill_s_value = self.compute_fill_s_value

        zongjiaobi = self.zongjiaobi

        create_date = self.create_date

        old_vm = self.old_vm
        old_s = self.old_s
        old_unit_price = self.old_unit_price
        new_vm = self.new_vm
        new_s = self.new_s
        new_unit_price = self.new_unit_price

        old_jiaotan_s = self.old_jiaotan_s
        new_jiaotan_s = self.new_jiaotan_s




        DB_HOST_MPP_DB2 = '10.70.48.41'
        DB_PORT_MPP_DB2 = 50021
        DB_DBNAME_MPP_DB2 = 'BGBDPROD'
        DB_USER_MPP_DB2 = 'm1admin'
        DB_PASSWORD_MPP_DB2 = 'm1adminbdzg'

        def getConnectionDb2(host, port, dbname, user, password):
            # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
            engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                                   encoding="utf-8", poolclass=NullPool)
            return engine.connect()

        # db_conn_mpp = getConnectionDb2(DB_HOST_MPP_DB2,
        #                                DB_PORT_MPP_DB2,
        #                                DB_DBNAME_MPP_DB2,
        #                                DB_USER_MPP_DB2,
        #                                DB_PASSWORD_MPP_DB2)
        if old_jiaotan_s == '' or new_jiaotan_s == '':
            print('没传入焦炭S')
            if old_vm == '' or old_s == '' or new_vm == '' or new_s == '':
                print('没有传入炼焦煤煤质')
                print('此时必须要有create_date才能算出焦炭S')
                if create_date == '':
                    msg = '无法计算出焦炭硫分'
                    return msg, result_list
                else:
                    msg, table_1 = Predict_6623Job(p_create_date=str(create_date)).execute()
                    old_jiaotan_s = table_1[0]['COKING_COKE_SULCONT']
                    new_jiaotan_s = table_1[1]['COKING_COKE_SULCONT']
                    old_vm = table_1[0]['COKE_VM']
                    new_vm = table_1[1]['COKE_VM']
                    old_s = table_1[0]['S']
                    new_s = table_1[1]['S']
                    old_unit_price = table_1[0]['UNIT_PRICE']
                    new_unit_price = table_1[1]['UNIT_PRICE']
            else:
                print('通过炼焦煤挥发分硫分计算焦炭S')
                # sql = " select PARM_CHN,PARM_CALC " \
                #       " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_COEF2 " \
                #       " where TMPL_NO ='original' "
                # data_jiaotancanshu = pd.read_sql_query(sql, con=db_conn_mpp)
                data_jiaotancanshu = pd.read_excel('COKE2参数.xlsx')
                data_jiaotancanshu.columns = data_jiaotancanshu.columns.str.upper()
                parm_4 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_4')]
                parm_4 = parm_4['PARM_CALC'].values[0]
                parm_5 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_5')]
                parm_5 = parm_5['PARM_CALC'].values[0]
                parm_6 = data_jiaotancanshu[(data_jiaotancanshu['PARM_CHN'] == 'parm_6')]
                parm_6 = parm_6['PARM_CALC'].values[0]
                old_jiaotan_s = parm_4 + parm_5 * (old_s) / (100 - old_vm) - parm_6 * (old_vm)
                new_jiaotan_s = parm_4 + parm_5 * (new_s) / (100 - new_vm) - parm_6 * (new_vm)
        old_jiaotan_s = float(old_jiaotan_s)
        new_jiaotan_s = float(new_jiaotan_s)
        delta_jiaotan_s = new_jiaotan_s - old_jiaotan_s
        delta_dairuliu = delta_jiaotan_s * zongjiaobi

        old_dairuliu = compute_fill_s_value
        new_dairuliu = old_dairuliu + delta_dairuliu
        y_pred_old = Predict_6621Job(p_mode=mode, p_bf_no=bf_no, p_avg_iron_temp=avg_iron_temp,
                                        p_avg_c_s_value=avg_c_s_value, p_compute_slag_rate=compute_slag_rate,
                                        p_compute_fill_s_value=old_dairuliu).execute()
        y_pred_new = Predict_6621Job(p_mode=mode,p_bf_no=bf_no, p_avg_iron_temp=avg_iron_temp,
                                        p_avg_c_s_value=avg_c_s_value, p_compute_slag_rate=compute_slag_rate,
                                        p_compute_fill_s_value=new_dairuliu).execute()

        df_out = pd.DataFrame(columns=['PLAN_NAME', 'COKE_VM', 'S', 'UNIT_PRICE', 'COKING_COKE_SULCONT', 'COMPUTE_FILL_S_VALUE', 'AVG_S_VALUE'])
        dict = {}

        dict['PLAN_NAME'] = '调整炼焦煤配比前'
        dict['COKE_VM'] = old_vm
        dict['S'] = old_s
        dict['UNIT_PRICE'] = old_unit_price
        dict['COKING_COKE_SULCONT'] = old_jiaotan_s
        dict['COMPUTE_FILL_S_VALUE'] = old_dairuliu
        dict['AVG_S_VALUE'] = y_pred_old
        new_row = pd.Series(dict)
        df_out = df_out.append(new_row, ignore_index=True)
        dict = {}

        dict['PLAN_NAME'] = '调整炼焦煤配比后'
        dict['COKE_VM'] = new_vm
        dict['S'] = new_s
        dict['UNIT_PRICE'] = new_unit_price
        dict['COKING_COKE_SULCONT'] = new_jiaotan_s
        dict['COMPUTE_FILL_S_VALUE'] = new_dairuliu
        dict['AVG_S_VALUE'] = y_pred_new
        new_row = pd.Series(dict)
        df_out = df_out.append(new_row, ignore_index=True)
        msg = '运行成功'
        result_list = df_out.to_dict(orient='records')


        print('finish')
        return msg, result_list

